﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_servers_needing_config]
#-- Purpose:		Retrieves a list of servers needing configuration
#--	Last Update:	08/12/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_servers_needing_config]
AS

SET NOCOUNT ON

SELECT		ssd.ssd_sqlserver_name_ro,
			ssd.ssd_server_name,
			ssd.ssd_type_ro,
			sql.sql_has_master_jobs,
			sql.sql_has_centraldba_db,
			sql.sql_backup_server_ro,
			(CASE	WHEN ssd.ssd_instance_name IS NULL THEN 'MSSQLSERVER'
					ELSE 'MSSQL$' + ssd.ssd_instance_name
					END) mssqlServiceName,
			(CASE	WHEN ssd.ssd_instance_name IS NULL THEN 'SQLSERVERAGENT'
					ELSE 'SQLAGENT$' + ssd.ssd_instance_name
					END) agentServiceName,
			ssd.ssd_sql_service
FROM		dbo.tblMSX_server_sql_info sql
JOIN		dbo.tblMSX_server_discovery ssd ON sql.sql_ssd_id = ssd.ssd_id
WHERE		ssd.ssd_is_vendor_managed_ro = 0							--- Don't deploy to vendor managed
			AND ssd.ssd_is_disabled = 0									--- Don't deploy to disabled servers
			AND sql.sql_is_msx_instance = 0								--- Don't deploy to the MSX server
			AND ISNULL(sql.sql_product_version, '') NOT LIKE '8.%'		--- Don't deploy to SQL Server 2000
			AND (
				ISNULL(sql_has_master_jobs, 0) = 0
				OR ISNULL(sql_has_centraldba_db, 0) = 0
				OR sql_backup_server_ro IS NULL
			)

SET NOCOUNT OFF